• L3 MIASHS/Ingémath/METIS
• Université Paris Cité
• Année 2024-2025
• Course Homepage
  
• Moodle

Dans une métropole, un exploitant de vélos partagés cherche à archiver des informations concernant les vélos et les stations.

Chaque vélo est identifié par un numéro. Un vélo possède une date de mise en service, un type (électrique/mécanique). Un vélo possède éventuellement une date de retrait de service.

Une station est identifiée par un numéro. Une station possède un nom. Une station possède une position (lattitude, longitude) et une altitude. Une station possède un nombre de fixe de bornes d’accrochages.

Un trajet est effectué par un vélo entre un instant (timestamp) de départ et un instant d’arrivée. Un trajet part d’une station et arrive à une station (pas toujours différente).

Question

Proposer un diagrammme entité-association correspondant à cette modélisation.

Solution

trajet est une association ternaire avec des cardinalités 0:n. C’est une association plusieurs-plusieurs.

Question

Si besoin, transformez votre schéme de façon à ce qu’il ne comporte plus d’associations d’arité supérieure ou égale à 3, ou d’associations plusieurs-plusieurs (0:n pour plusieurs entités participantes).

Solution

Question

Proposer une traduction en pattes de corbeau du diagramme EA proposé en réponse
à la première question.

Solution

erDiagram
    TRAJET }o--|| VELO : velo_num 
    TRAJET }o--|| STATION  : origin_num
    TRAJET }o--|| STATION : dest_num 

  VELO {
    integer num PK
    text type 
    date mise_service_date
    date retrait-date  
  }

  STATION {
    integer num PK
    numeric lat
    numeric long
    text name
    integer docks 
  }

  TRAJET {
    integer velo_num PK, FK
    integer origin_num PK, FK
    integer dest_num FK
    timestamp start PK
    timestamp end 
  }

Question

Explicitez d’éventuelles contraintes externes (si vous l’avez déjà fait en marge du diagramme entité-association, contentez-vous d’un renvoi).

Solution

1. La date de retrait de service d’un vélo ne peut pas précéder sa mise en service (CHECK)
2. Un vélo ne peut pas être utilisé pour un trajet s’il nest pas en service (contrainte multitable, réalisable avec une fonction qui renvoie l’intervalle pendant lequel un vélo est en service et une contrainte CHECK qui vérifie que (start,end ) est inclus dans l’intervalle de service)
3. Le début d’un trajet doit précéder sa fin (CHECK)
4. Un même vélo ne peut pas effectuer deux trajets simultanément (EXCLUDE)

Attention

Dans la suite, vous formulerez les requêtes dans le schéma relationnel défini par votre schéma en pattes de corbeau.

Question

Pour chaque station, chaque heure de la journée, comptez le nombre de trajets partis de la station.

PostgreSQL propose un type timestamp (qui permet de représenter les instants à la microseconde près). PostgreSQL propose une fonction fonction date_part(text, timestamp) qui permet d’extraire les différents composants d’un objet de type timestamp. date_part('month', timestamp) extrait le mois grégorien (un entier de 1 à 12), date_part('hour', timestamp) extrait l’heure du jour (un entier de 0 à 23).

Solution

SELECT 
  tr.origin_num, date_part('hour', start) AS heure,
  count(*) AS n_trajet
FROM 
  trajet tr
GROUP BY 
  tr.origin_num, date_part('hour', start) ;

Question

Pour chaque vélo, comptez le nombre de trajets de plus d’une heure effectués.

En PostgreSQL, la différence de deux objets de type timestamp est un objet de type interval (durée). Pour définir une constante correspondant à une durée d’un jour, d’un mois, d’une heure, vous pouvez utiliser la syntaxe '1 day'::interval, '1 month'::interval, '1 hour'::interval.

Solution

SELECT
  tr.velo_num,
  count(*) AS nb_trajets
FROM 
  trajet tr
WHERE 
  (tr.end IS NOT NULL AND (tr.start - tr.end) > '1 hour'::interval)   OR
  (tr.end IS NULL AND (current_timestamp - tr.start) > '1 hour'::interval)
GROUP BY 
  tr.velo_num ;

Question

Pour chaque jour de la semaine, listez les quatre couples (station de départ, station d’arrivée) utilisés par le plus grand nombre de trajets ce jour de la semaine là.

En PostgreSQL, pour extraire le jour de la semaine d’un objet ts de type timestamp, vous pouvez utiliser EXTRACT(DOW FROM ts). Le résultat est un entier entre 0 et 6, 0 pour dimanche, 1 pour lundi, …, 7 pour samedi.

postgres=# SELECT 
  current_timestamp as maintenant , 
  EXTRACT(DOW FROM current_timestamp + '1 day'::interval) as dow_demain ;

          maintenant           | dow_demain 
-------------------------------+------------
 2024-12-08 10:55:05.672936+01 |          1

Solution

WITH R AS 
(
   SELECT 
     EXTRACT(DOW FROM tr.start) AS jds, 
     tr.origin_num,  tr.dest_num, 
     COUNT(*) AS nb_trajets
   FROM 
     trajet tr 
   GROUP BY
     EXTRACT(DOW FROM tr.start), 
     tr.origin_num, 
     tr.dest_num
), S AS 
(
  SELECT 
    R.*, 
    RANK() OVER (PARTITION BY R.jds ORDER BY R.nb_trajets DESC) AS rnk
  FROM 
    R
)

SELECT 
  S.*
FROM 
  S
WHERE S.rnk <= 4 ;

Question

Soit \(\mathcal{A} = \{\texttt{A, B, C, D, E, H, S, T}\}\) un schéma.

Soit Σ = {{A, E} \(⟶\) {H}, {S, T} \(⟶\) {C, E}, {S} \(⟶\) {A}, {B} \(⟶\) {T}}

• Quelle est la fermeture de {B, S, E} ?
• Quelles sont les clés de \(\mathcal{A}, \Sigma\) ?
• \(\mathcal{A}, \Sigma\) est elle forme normale de Boyce-Codd ?

Solution

• \(\texttt{[B, S, E]}_{\Sigma}^+ = \{ \texttt{B, S, E, T, C, A, H}\} = \{ \texttt{A, B, C, E, H, S, T}\}\)
• Toute clé contient \(\texttt{B, D, S}\) qui est une super-clé \([\texttt{B, D, S}]^+_\Sigma = \{\texttt{B, D, S, A, T, C, E, H}\}\). C’est la seule clé.
• Le schéma n’est pas en FNBC, toutes les DF de Σ ont un déterminant qui n’est pas une super-clé.